package models

import (
	//	"errors"
	//	"fmt"
	//	"reflect"
	"strings"
	//	"time"
	// "ga_wz_backend/libs/strtool"

	"github.com/astaxie/beego"
	"github.com/astaxie/beego/orm"
)

//Desc 统计单位情况
//return 统计结果，异常
func StaticOrg() ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT a.id,a.abbr as name," +
		" Count(b.organization_id) AS clicks," +
		" sum(d.logincount) as logincount," +
		" Sum(b.looklong) AS looklong " +
		" FROM ((public.sys_organization a LEFT JOIN public.vid_watchhistory b" +
		" ON a.id= b.organization_id) " +
		" left JOIN public.sys_user c ON a.id = c.organization_id)" +
		" left JOIN public.sys_user d on d.id = c.id " +
		" where a.status = 1 and a.isdelete=0  " +
		" GROUP BY a.id " +
		" ORDER BY a.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["logincount"] == nil {
			v["logincount"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc  统计全部视频观看情况
//return []orm.Params, error
func StaticVideo() ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM vid_video b LEFT JOIN public.vid_watchhistory a " +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据单位统计视频观看情况
//Param user *User
//return []orm.Params, error
func StaticVideoByOrgId(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM vid_video b LEFT JOIN public.vid_watchhistory a " +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" and b.organization_id = ? " +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql, user.Organization.Id).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据部门统计视频观看情况
//Param user *User
//return []orm.Params, error
func StaticVideoByDept(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	str := strings.Join(*GetDeptChildrenStr(user.Dept[0].Id), "','")
	str = "'" + str + "'"
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM vid_video b LEFT JOIN public.vid_watchhistory a " +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" and b.dept_id in ( " + str + " )  " +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()

	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc  统计全部直播观看情况
//return []orm.Params, error
func StaticLive() ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM vid_live b LEFT JOIN public.vid_watchhistory a" +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据单位统计直播观看情况
//Param user *User
//return []orm.Params, error
func StaticLiveByOrgId(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM vid_live b LEFT JOIN public.vid_watchhistory a" +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" and b.organization_id =? " +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql, user.Organization.Id).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据部门统计直播观看情况
//Param user *User
//return []orm.Params, error
func StaticLiveByDept(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	//	str := libs.SliceToStr(",", *GetDeptChildrenStr(user.Dept[0].Id))
	str := strings.Join(*GetDeptChildrenStr(user.Dept[0].Id), "','")
	str = "'" + str + "'"
	sql := " SELECT b.id,b.title AS name," +
		" count(a.vid_id) AS clicks," +
		" Sum(a.looklong) AS looklong " +
		" FROM vid_live b LEFT JOIN public.vid_watchhistory a" +
		" on b.id=a.vid_id" +
		" where b.isdelete=0 and b.status=1" +
		" and b.dept_id in ( " + str + " )  " +
		" GROUP BY b.id,b.title " +
		" ORDER BY b.id ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 统计全部人员的登录及观看情况
//Param
//return 统计结果，异常
func StaticUser() ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT a.id,a.name as code,a.abbr as uname," +
		" d.name as oname,f.name as dname," +
		" a.logincount,Sum(c.looklong)as looklong ," +
		" count(c.user_id) as clicks " +
		" FROM   public.sys_user a " +
		" LEFT JOIN  public.vid_watchhistory c ON a.id=c.user_id" +
		" LEFT JOIN sys_organization d on a.organization_id= d.id" +
		" LEFT JOIN sys_user_sys_depts e on a.id=e.sys_user_id" +
		" LEFT JOIN sys_dept f on e.sys_dept_id=f.id" +
		" WHERE a.status = 1 and a.isdelete=0" +
		" GROUP BY a.id,a.name,a.logincount,d.name,f.name," +
		" a.abbr" +
		" ORDER BY a.Organization_id DESC"
	o := orm.NewOrm()
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["logincount"] == nil {
			v["logincount"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据单位统计人员的登录及观看情况
//Param user *User
//return 统计结果，异常
func StaticUserByOrgId(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	sql := " SELECT a.id,a.name as code,a.abbr as uname, " +
		" d.name as oname,f.name as dname, " +
		" a.logincount,Sum(c.looklong)as looklong , " +
		" count(c.user_id) as clicks " +
		" FROM public.sys_user a " +
		" LEFT JOIN  public.vid_watchhistory c ON a.id=c.user_id " +
		" LEFT JOIN sys_organization d on a.organization_id= d.id " +
		" LEFT JOIN sys_user_sys_depts e on a.id=e.user_id " +
		" LEFT JOIN sys_dept f on e.dept_id=f.id " +
		" WHERE a.status = 1 and a.isdelete=0 " +
		"  and a.Organization_id = ? " +
		" GROUP BY a.id,a.name,a.logincount,d.name,f.name, " +
		" a.abbr " +
		" ORDER BY a.Organization_id DESC"
	o := orm.NewOrm()
	_, err := o.Raw(sql, user.Organization.Id).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["logincount"] == nil {
			v["logincount"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 根据部门统计人员的登录及观看情况
//Param user *User
//return 统计结果，异常
func StaticUserByDept(user *User) ([]orm.Params, error) {
	var maps []orm.Params

	// str := strtool.SliToStr(",", *GetDeptChildrenStr(user.Dept[0].Id))
	str := strings.Join(*GetDeptChildrenStr(user.Dept[0].Id), "','")
	str = "'" + str + "'"
	sql := " SELECT a.id,a.name as code,a.abbr as uname, " +
		" d.name as oname,f.name as dname,  " +
		" a.logincount,Sum(c.looklong)as looklong ,  " +
		" count(c.user_id) as clicks " +
		" FROM  public.sys_user a  " +
		" LEFT JOIN  public.vid_watchhistory c ON a.id=c.user_id  " +
		" LEFT JOIN sys_organization d on a.organization_id= d.id  " +
		" LEFT JOIN sys_user_sys_depts e on a.id=e.user_id  " +
		" LEFT JOIN sys_dept f on e.dept_id=f.id  " +
		" WHERE a.status = 1 and a.isdelete=0  " +
		" and e.dept_id in ( " + str + " )  " +
		" GROUP BY a.id,a.name,a.logincount,d.name,f.name,  " +
		" a.abbr " +
		" ORDER BY a.Organization_id DESC "
	o := orm.NewOrm()
	beego.Debug(sql)
	beego.Debug(str)
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["logincount"] == nil {
			v["logincount"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//Desc 统计部门情况,包括下属部门
//Param user *User
//return 统计结果，异常
func StaticDept(user *User) ([]orm.Params, error) {
	var maps []orm.Params
	str := GetDeptSql(user.Dept[0].Id)
	sql := " SELECT a.id,a.name as name," +
		" Count(b.dept_id) AS clicks," +
		" sum(d.logincount) as logincount," +
		" Sum(b.looklong) AS looklong " +
		" FROM ((public.sys_dept a LEFT JOIN public.vid_watchhistory b" +
		" ON a.id= b.dept_id) " +
		" left JOIN public.sys_user_sys_depts c ON a.id = c.dept_id)" +
		" left JOIN public.sys_userprof d on d.id = c.user_id" +
		" where a.status = 1 and a.isdelete=0" +
		" and a.organization_id= ?" + str +
		" GROUP BY a.id " +
		" ORDER BY a.seq ASC"
	o := orm.NewOrm()
	_, err := o.Raw(sql, user.Organization.Id).Values(&maps)
	for _, v := range maps {
		if v["clicks"] == nil {
			v["clicks"] = 0
		}
		if v["logincount"] == nil {
			v["logincount"] = 0
		}
		if v["looklong"] == nil {
			v["looklong"] = 0
		}
	}
	return maps, err
}

//StaticUsedDiskAll 查看all单位已用空间的百分比
//int64 已用空间百分比
func StaticUsedDiskAll() ([]orm.Params, error) {
	o := orm.NewOrm()
	var maps []orm.Params
	sql := "SELECT a.name as name,a.userlimit as olimit," +
		"sum(b.size) as oused" +
		" FROM sys_organization a left JOIN vid_video b on " +
		" a.id=b.organization_id " +
		" GROUP BY a.name,a.userlimit"
	_, err := o.Raw(sql).Values(&maps)
	for _, v := range maps {
		if v["oused"] == nil {
			v["oused"] = 0
		}
	}
	return maps, err
}
